The present invention relates to database systems, and in particular, using database systems in a hosting environment to store data for users.
Use of commercial off-the-shelf applications (xe2x80x9cpackaged applicationsxe2x80x9d) has proliferated. Enterprises are buying packaged applications instead of developing in-house applications, avoiding the higher cost associated with developing their own in-house applications. The kinds of packaged applications that may be purchased include applications for financial processing, manufacturing work-flow, human resources, and customer relationship management.
In addition to buying packaged applications, the companies are employing service companies to maintain the packaged applications and the computer systems upon which the applications run. One technique used by service companies to maintain and operate packaged applications is referred to as application hosting. Application hosting refers to a host (e.g. a company) that maintains applications for multiple enterprises on one or more computer systems, using the same computer infrastructure to run all the packaged applications. The term hosting environment refers to all the various components being maintained for an enterprise, including application components and computer infrastructure components (e.g. operating system, hardware). A hosting environment may be accessed via, for example, the Internet, or an extended intranet. Application hosting can reduce the cost of managing applications because it allows companies to share the resources needed to run a packaged application, resources which include computer components, application experts, and computer administrative support personnel, all of which are needed to operate an application.
The term enterprise is used herein to refer to a particular entity for whom an application and its associated data is being hosted. The entity may be a human individual or an organization, including, without limitation, a business.
A typical hosting environment typically follows the xe2x80x9csiloxe2x80x9d model. Under the silo model, limited components in the environment are shared by enterprises while most components are maintained for each enterprises.
FIG. 1 is a block diagram used to depict silo model 101. Silo model 101 depicts hosting environment component layers 110, 120, 130, 140, 150, and 160. Machine layer 10 represents the various hardware components used in a hosting environment, such as computers and disk drives. Operating system layer 120 represents the operating system used in a hosting environment, database system layer 130 corresponds to the database systems used in a hosting environment, schema layer 140 represents a collection of database objects in the database systems, database object layer 150 refers to the database objects in schemas. Application layer 160 refers to hosted application software.
Machine layer 110 and operating system layer 120 are typically shared while the remaining layers are typically not shared by multiple enterprises. Thus, a separate instance of a database system and application software is created and maintained for a hosted enterprise. These separate instances are referred to as a silo. For example, silo 171 and 172 are instances of unshared database system and application software components for a particular enterprise.
Whether a hosting environment component can be shared affects the xe2x80x9cscalabilityxe2x80x9d of the hosting environment. The term xe2x80x9cscalabilityxe2x80x9d, as used herein, refers to the rate at which more resources are needed to host additional enterprises. A hosting environment scales better when less additional resources are needed to support new enterprises.
Sharing operating system and machine layers 110 and 120 promotes better scalability. An additional enterprise does not require installation of another operating system. On the other hand, the unshared nature of database system layer 130 and application layer 160 impedes scalability. Adding an additional enterprise requires installation of another instance of the database system and application software. In general, adding another instance of a hosting environment component to support an additional enterprise requires greater additional resources than would be otherwise required by using an already existing component to support the additional enterprise. Adding an additional instance of another hosting environment component requires more labor to install and maintain than simply reconfiguring and maintaining an existing instance to support another enterprise.
Improved scalability may be achieved by sharing more hosting environment component layers. For example, a single database system may be used for multiple enterprises. The application instances that access the database system access data in separate schemas within the database system. Each schema contains database objects for a particular enterprise. For example, data for one hosted payroll application may be stored in a table PAYROLL in one schema, while data for another hosted payroll application may be stored in a table PAYROLL in another schema.
To further improve scalability, application software and database objects may be shared. However, this is more problematic. Typically, application software is not developed with the features needed to use one instance of the application software to handle multiple enterprises. For example, application software is not configured to restrict user access to data according to the enterprise of the user accessing the data. Typically, an enterprise desires to separate its data from the data of another enterprise, and to confine access to its data to the users belonging to the enterprise. However, an instance of the application software typically uses one schema or set of database objects to store data, and provides no mechanism to logically or physically separate the data of multiple enterprises within a single set of database objects, let alone restrict user access to the separate data of the enterprise of the user.
Legacy application software may be re-engineered to restrict access to data according to the enterprise of the user. However, such modifications can be very expensive. For example, every database command programmed for an application may have to be examined and possibly rewritten so that the database command requests access to only the data of a particular enterprise. The term database command refers to commands that request the retrieval, selection, insertion, and modification of records. Typically, database commands conform to a database language. For example, a query that conforms to SQL.
New application software may be developed to handle multiple enterprises. However, developing software with this capability requires greater development effort and costs more. For example, queries that are developed to limit access to data of a particular enterprise are more complicated to program.
Based on the foregoing, it is clearly desirable to provide a mechanism that allows data for multiple enterprises that is generated by a single instance of an application to be stored in the same set of database objects while minimizing the cost of developing or redeveloping such applications.
Techniques are provided for storing data of multiple enterprises in a shared set of database objects in a database system and allowing enterprise users to interact with the database system as if the set of database objects contained only their data. According to an aspect of the present invention, a database command issued against a database object by an enterprise user is modified by adding predicates that limit access to data associated with the enterprise. The predicates may specify conditions based on a column in the database object that identifies the enterprise of the user. When a user issues a database command to add data to the database object, the column is populated in a manner transparent to the user. In one variation, the data in the database object that is associated with different enterprises is stored in different tablespaces.